﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Security.Cryptography;
using System.Text;
using System.IO;

/// <summary>
/// Summary description for UserService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class UserService : System.Web.Services.WebService {

    public UserService () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }
    private Database db = DatabaseFactory.CreateDatabase("cns");
    [WebMethod]
    public string GetPassword(string username)
    {
        var password = db.ExecuteScalar("sp_GetPassword", username);
        if (password == null)
            return null;
        return password.ToString();
    }
    [WebMethod]
    public object GetDisplayName(string username)
    {
        var displayname = db.ExecuteScalar("sp_GetUserDisplayName", username);
        if (displayname == null)
            return null;
        return displayname;
    }
    [WebMethod]
    public object GetEmployeeID(string username)
    {
        var employeeid = db.ExecuteScalar("sp_GetEmployeeID", username);
        if (employeeid == null)
            return null;
        return employeeid;
    }
    [WebMethod]
    public object GetFormID(string formname)
    {
        var formid = db.ExecuteScalar("sp_GetFormID", formname);
        if (formid == null)
            return null;
        return formid;
    }
    [WebMethod]
    public DataTable GetFormCaption(string formname)
    {
        DataTable table = new DataTable();
        table.TableName = "formcaption";
        var formcaption = db.ExecuteDataSet("sp_GetFormCapion", formname);
        if (formcaption.Tables.Count >= 0)
            table = formcaption.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetUserInfo(string username)
    {
        DataTable table = new DataTable();
        table.TableName = "user";
        var userinfo = db.ExecuteDataSet("sp_GetUserInfo", username);
        if (userinfo.Tables.Count >= 0)
            table = userinfo.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetGroupInfo(string groupname)
    {
        DataTable table = new DataTable();
        table.TableName = "group";
        var groupinfo = db.ExecuteDataSet("sp_GetGroupInfo", groupname);
        if (groupinfo.Tables.Count >= 0)
            table = groupinfo.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetGroupPermission(string groupname)
    {
        DataTable table = new DataTable();
        table.Columns.Add("ParentID", typeof(int));
        table.Columns.Add("ViCaption", typeof(string));
        table.Columns.Add("CanOpen", typeof(bool));
        table.Columns.Add("ObjectName", typeof(string));
        table.Columns.Add("MenuID", typeof(int));
        table.TableName = "grouppermission";
        var grouppermission = db.ExecuteDataSet("sp_GetGroupPermission", groupname);
        if (grouppermission.Tables.Count >= 0)
            table = grouppermission.Tables[0];
        table.PrimaryKey = new DataColumn[] { table.Columns[1] };
        return table;
    }
    [WebMethod]
    public DataTable GetUserPermission(string groupname)
    {
        DataTable table = new DataTable();
        table.Columns.Add("ParentID", typeof(int));
        table.Columns.Add("ViCaption", typeof(string));
        table.Columns.Add("CanOpen", typeof(bool));
        table.Columns.Add("ObjectName", typeof(string));
        table.TableName = "userpermission";
        var grouppermission = db.ExecuteDataSet("sp_GetUserPermission", groupname);
        if (grouppermission.Tables.Count >= 0)
            table = grouppermission.Tables[0];
        table.PrimaryKey = new DataColumn[] { table.Columns[3] };
        return table;
    }
    [WebMethod]
    public DataTable GetUserList()
    {
        DataTable table = new DataTable();
        table.TableName = "userlist";
        var userlist = db.ExecuteDataSet(CommandType.StoredProcedure, "sp_UsersList");
        if (userlist.Tables.Count >= 0)
            table = userlist.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetGroupList()
    {
        DataTable table = new DataTable();
        table.TableName = "grouplist";
        var grouplist = db.ExecuteDataSet(CommandType.StoredProcedure, "sp_GroupList");
        if (grouplist.Tables.Count >= 0)
            table = grouplist.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetEmployeeList()
    {
        DataTable table = new DataTable();
        table.TableName = "employeelist";
        var employeelist = db.ExecuteDataSet(CommandType.StoredProcedure, "sp_GetEmployeeInfo");
        if (employeelist.Tables.Count >= 0)
            table = employeelist.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetFormList(int formid)
    {
        DataTable table = new DataTable();
        table.Columns.Add("ObjectName", typeof(string));
        table.Columns.Add("ViCaption", typeof(string));
        table.Columns.Add("EnCaption", typeof(string));
        table.Columns.Add("ImageID", typeof(int));
        table.TableName = "formlist";
        var formlist = db.ExecuteDataSet("sp_GetFormList", formid);
        if (formlist.Tables.Count >= 0)
            table = formlist.Tables[0];
        table.PrimaryKey = new DataColumn[] { table.Columns[0] };
        return table;
    }
    [WebMethod]
    public DataTable GetMenuList()
    {
        DataTable table = new DataTable();
        table.TableName = "employeelist";
        var formlist = db.ExecuteDataSet(CommandType.StoredProcedure, "sp_GetMenuList");
        if (formlist.Tables.Count >= 0)
            table = formlist.Tables[0];
        return table;
    }
    [WebMethod]
    public DataTable GetControlList(string formname)
    {
        DataTable table = new DataTable();
        table = GetFormList(int.Parse(GetFormID(formname).ToString()));
        return table;
    }


    [WebMethod]
    public bool VerifyMd5Hash(string input, string hash)
    {
        // Hash the input.
        string hashOfInput = GetMd5Hash(input);

        // Create a StringComparer an compare the hashes.
        StringComparer comparer = StringComparer.OrdinalIgnoreCase;

        if (0 == comparer.Compare(hashOfInput, hash))
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    [WebMethod]
    public string GetMd5Hash(string textInput)
    {
        MD5 md5Hasher = MD5.Create();
        // Convert the input string to a byte array and compute the hash.
        byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(textInput));

        // Create a new Stringbuilder to collect the bytes
        // and create a string.
        StringBuilder sBuilder = new StringBuilder();

        // Loop through each byte of the hashed data 
        // and format each one as a hexadecimal string.
        for (int i = 0; i < data.Length; i++)
        {
            sBuilder.Append(data[i].ToString("x2"));
        }
        // Return the hexadecimal string.
        return sBuilder.ToString();
    }

    #region Excute Methods
    [WebMethod]
    public int UpdateUser(int employeeid, string groupid, string username, string displayname, string password)
    {
        int result = db.ExecuteNonQuery("sp_UpdateUser", employeeid, groupid, username, displayname, password);
        return result;
    }
    [WebMethod]
    public int UpdateMenu(int FormID, string ObjectName, string ViCaption, string EnCaption, string ImageId)
    {
        int result = db.ExecuteNonQuery("sp_UpdateMenu", FormID, ObjectName, ViCaption, EnCaption, ImageId);
        //System.Data.Common.DbCommand cm = db.GetStoredProcCommand("sp_UpdateMenu", FormID, ObjectName, ViCaption, EnCaption, ImageId);
        //File.AppendAllText(@"C:\ds.txt", cm.);
        //File.AppendAllText(cm.CommandText, "\n");
        return result;
    }
    [WebMethod]
    public int BackupDatabase(string dbname, string dbpath)
    {
        int result = db.ExecuteNonQuery("sp_BackupDatabase", dbname, dbpath);
        return result;
    }
    [WebMethod]
    public int RestoreDatabase(string dbname, string filename)
    {
        Database dbrs = DatabaseFactory.CreateDatabase("master");
        int result = dbrs.ExecuteNonQuery("sp_RestoreDatabase", dbname, filename);
        return result;
    }

    [WebMethod]
    public int SaveUserInfo(string username, DateTime lastlogin)
    {
        int result = db.ExecuteNonQuery("sp_UpdateUserLastLogin", username, lastlogin);
        return result;
    }
    [WebMethod]
    public int UpdateForm(string FormName, string ViCaption, string EnCaption)
    {
        int result = db.ExecuteNonQuery("sp_UpdateForm", FormName, ViCaption, EnCaption);
        return result;
    }
    [WebMethod]
    public int ChangePassword(string username, string newpassword)
    {
        newpassword = GetMd5Hash(newpassword);
        int result = db.ExecuteNonQuery("sp_ChangePassword", username, newpassword);
        return result;
    }
    [WebMethod]
    public int DeleteUser(string username)
    {
        int result = db.ExecuteNonQuery("sp_DeleteUser", username);
        return result;
    }
    [WebMethod]
    public int UpdateUserPermission(string groupname, int menuid, bool canopen)
    {
        int result = db.ExecuteNonQuery("sp_UpdateGroupPermission", groupname, menuid, canopen);
        return result;
    }
    [WebMethod]
    public int UpdateGroup(string groupid, string groupname, string description)
    {
        int result = db.ExecuteNonQuery("sp_UpdateGroup", groupid, groupname, description);
        return result;
    }
    [WebMethod]
    public int AddEmployee(string FirstName,string LastName,bool Gender,DateTime Birthday,string EmployeeType,string Address,string Email,string Phone,string Mobile)
    {
        int result = db.ExecuteNonQuery("sp_AddEmployee", FirstName, LastName, Gender, Birthday, EmployeeType, Address, Email, Phone, Mobile);
        return result;
    }
    #endregion
    
}
